package edu.misaka.moe.dao;



import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Objects;

import org.apache.log4j.Logger;
import org.junit.Test;

import edu.misaka.moe.dao.iface.UserDao;
import edu.misaka.moe.domain.Page;
import edu.misaka.moe.domain.UMQueryKeyword;
import edu.misaka.moe.domain.User;
import edu.misaka.moe.domain.UserDetail;
import edu.misaka.moe.utils.DBConnection;
import edu.misaka.moe.utils.JdbcTemplate;


public class UserDaoImpl implements UserDao{
		JdbcTemplate jt=new JdbcTemplate();
		private static Logger log=Logger.getLogger(UserDaoImpl.class);
			@Test
			public int queryUser(String name,String password) {
				
				String sql="select count(1) from users WHERE name=? and PASSWORD=?";
				int rowNum=jt.update(sql,name,password);
				System.out.println(rowNum);
				return rowNum;
			}

			@Override
			//添加用户表
			public int upUser(User user) {
				int userId=-1;
				String sql="insert into users values(users_id.nextval,?,?,?,?,?)";
				Connection conn=new DBConnection().getConnection();
				try {
					PreparedStatement psmt=conn.prepareStatement(sql,new String[] {"id"});
					psmt.setString(1, user.getName());
					psmt.setString(2, user.getNickName());
					psmt.setString(3, user.getPassword());
					psmt.setString(4, user.getSex());
					psmt.setString(5, user.getEmail());
					int rowNum=psmt.executeUpdate();           
					System.out.println("rowNum="+rowNum);
					ResultSet rs=psmt.getGeneratedKeys();     //取得插入的列的集合
					if(rowNum==1&&rs.next()) {
						userId=rs.getInt(1);
					}
				} catch (SQLException e) {
					
					log.debug("query by page method error,message is "+e.getMessage());
				}
				
				return userId;
			}

			@Override
			//添加用户的住址和爱好
			public int upUserDetail(UserDetail ud) {
				int rowNum=0;
				String sql="insert into userDetail values(userdetail_id.nextval,?,?,?)";
				rowNum=jt.update(sql,ud.getNative_code(),ud.getHobby_code(),ud.getUserId());
				return rowNum;
			}

			@Override
			public void queryByPage(Page<List<Map<String, Object>>> page) {
				String sql="select * from (" + 
						"  select baseTable.*,rownum as rn from (" + 
						"    select u.id,u.name,u.password,u.sex,u.email,np.name nativePlace_name,ud.hobby_code " + 
						"           from users u left outer join Userdetail ud on u.id=ud.userid " + 
						"           left outer join nativeplace np on ud.nativeplace_code=np.code order by u.id) baseTable " + 
						"  where rownum<=(?)*?) " + 
						"where rn>(?-1)*?";
				List<Map<String,Object>> list=JdbcTemplate.query(sql, (rs)->{
					List<Map<String,Object>> list0=new ArrayList<>();
					try {
						while(rs.next()) {
							Map<String,Object> map=new HashMap<>();
							for(int i=1,len=rs.getMetaData().getColumnCount();i<=len;i++){
								map.put(rs.getMetaData().getColumnName(i), rs.getObject(i));
							}
							list0.add(map);
						}
					} catch (SQLException e) {
						log.debug("query by page method error,message is "+e.getMessage());
					}
					return list0;
				}, new Object[] {page.getCurrentPage(),page.getRowNumber(),page.getCurrentPage(),page.getRowNumber(),});
				page.setPageData(list);
				
			}

			@Override
			public void getTotalRow(Page<List<Map<String, Object>>> page) {
				String sql="select count(1) from users u left outer join Userdetail ud on u.id=ud.userid" + 
						" left outer join nativeplace np on ud.nativeplace_code=np.code order by u.id";
				int totalRow=JdbcTemplate.queryForCount(sql, new Object[] {});
				
				page.setTotalRow(totalRow);
				
			}

			@Override
			public void queryByPage(Page<List<Map<String, Object>>> page, UMQueryKeyword qk) {
				List<Object> params=new ArrayList<>();
				if(!Objects.equals("",qk.getName())) {
					params.add("%"+qk.getName()+"%");
				}
				if(!Objects.equals("",qk.getSex())) {
					params.add(qk.getSex());
				}	
				if(!Objects.equals("",qk.getHobbyCode())) {
					params.add("%"+qk.getHobbyCode()+"%");
				}
				params.add(page.getCurrentPage());
				params.add(page.getRowNumber());
				params.add(page.getCurrentPage());
				params.add(page.getRowNumber());
				String beginSQL="select * from ( select baseTable.*,rownum as rn from (";
				String SQL="select u.id,u.name,u.password,u.sex,u.email,np.name nativePlace_name,ud.hobby_code " + 
						"from users u left outer join Userdetail ud on u.id=ud.userid " + 
						"left outer join nativeplace np on ud.nativeplace_code=np.code where 1=1 ";
				if(!Objects.equals("",qk.getName())) {
					SQL+="and u.name like ? ";
				}
				if(!Objects.equals("",qk.getSex())) {
					SQL+="and u.sex=? ";
				}
				if(!Objects.equals("",qk.getHobbyCode())) {
					SQL+="and ud.hobby_code like ?";		
				}	
				String endSQL=" order by u.id) baseTable where rownum<=(?)*?) where rn>(?-1)*?";
				List<Map<String,Object>> list=JdbcTemplate.query(beginSQL+SQL+endSQL, (rs)->{
					List<Map<String,Object>> list0=new ArrayList<>();
					try {
						while(rs.next()) {
							Map<String,Object> map=new HashMap<>();
							for(int i=1,len=rs.getMetaData().getColumnCount();i<=len;i++){
								map.put(rs.getMetaData().getColumnName(i), rs.getObject(i));
							}
							list0.add(map);
						}
					} catch (SQLException e) {
						log.debug("query by page method error,message is "+e.getMessage());
					}
					return list0;
				}, params.toArray());
				page.setPageData(list);
				
				
			}

			@Override
			public void getTotalRow(Page<List<Map<String, Object>>> page, UMQueryKeyword qk) {
				List<Object> params=new ArrayList<>();
				if(!Objects.equals("",qk.getName())) {
					params.add("%"+qk.getName()+"%");
				}
				if(!Objects.equals("",qk.getSex())) {
					params.add(qk.getSex());
				}	
				if(!Objects.equals("",qk.getHobbyCode())) {
					params.add("%"+qk.getHobbyCode()+"%");
				}
				String sql="select count(1) from users u left outer join Userdetail ud on u.id=ud.userid" + 
						" left outer join nativeplace np on ud.nativeplace_code=np.code where 1=1 ";
				if(!Objects.equals("",qk.getName())) {
					sql+="and u.name like ? ";
				}
				if(!Objects.equals("",qk.getSex())) {
					sql+="and u.sex=? ";
				}
				if(!Objects.equals("",qk.getHobbyCode())) {
					sql+="and ud.hobby_code like ?";
				}
				sql+=" order by u.id";
				int totalRow=JdbcTemplate.queryForCount(sql, params.toArray());
				page.setTotalRow(totalRow);
				
				
			}
}
